import sqlite3
import pandas as pd


def load_sqlite_to_dataframe(db_path: str, query: str) -> pd.DataFrame:
    """
    Load data from an SQLite database into a pandas DataFrame using a query.

    Args:
        db_path (str): Path to the SQLite database file.
        query (str): SQL query to execute.

    Returns:
        pd.DataFrame: DataFrame containing the query results.
    """
    try:
        # Connect to the SQLite database
        conn = sqlite3.connect(db_path)
        # Create a cursor
        cursor = conn.cursor()

        # Execute the query
        cursor.execute(query)

        # Fetch all rows
        rows = cursor.fetchall()

        # Get column names from cursor description
        columns = [description[0] for description in cursor.description]

        # Create a DataFrame from the results
        df = pd.DataFrame(rows, columns=columns)

        # Close the cursor and connection
        cursor.close()
        conn.close()

        return df

    except sqlite3.Error as e:
        print(f"SQLite error: {e}")
        return pd.DataFrame()


# Example usage
if __name__ == "__main__":
    # Path to your SQLite database file
    db_path = "path_to_your_database.db"

    # Example query
    query = "SELECT * FROM your_table_name"

    # Load data into a DataFrame
    df = load_sqlite_to_dataframe(db_path, query)

    # Print the DataFrame
    print(df)

    # Example: Assuming you have predicted and ground truth queries
    predicted_query = "SELECT col1, col2 FROM your_table_name WHERE condition"
    ground_truth_query = "SELECT col1, col2 FROM your_table_name WHERE condition"

    predicted_df = load_sqlite_to_dataframe(db_path, predicted_query)
    ground_truth_df = load_sqlite_to_dataframe(db_path, ground_truth_query)

    # Now you can use these DataFrames with the compute_soft_f1 function
    # from the previous code
    simple_f1, moderate_f1, challenging_f1, all_f1, counts = compute_soft_f1(
        predicted_df, ground_truth_df, diff_json_path=None
    )

    print(f"Simple F1: {simple_f1:.2f}%")
    print(f"Moderate F1: {moderate_f1:.2f}%")
    print(f"Challenging F1: {challenging_f1:.2f}%")
    print(f"Overall F1: {all_f1:.2f}%")
    print(f"Counts: {counts}")